﻿using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.JsonPatch.Adapters;
using Microsoft.AspNetCore.Mvc;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using VOL.Core.DBManager;
using Dapper;
using System.Data;
using VOL.MyTable.Services;
using VOL.Entity.DomainModels;
using VOL.MyTable.Repositories;

namespace VOL.WebApi.Controllers.MyTable.bigscreen
{
    [Route("api/fkbigscreen")]
    [ApiController]
    public class FkBigScreenController : Controller
    {
        [HttpPost, HttpGet, Route("get_kanb"), AllowAnonymous]
        public object Get_kanb(string racct, string rcntr)
        {
            Hashtable rtn_hst = new Hashtable();
           // string sql = "";
           IQueryable<Fk_yusuan_view_unpivot> Fk_yusuan_view_unpivot = DBServerProvider.DbContext.Set<Fk_yusuan_view_unpivot>().Where(w=>w.gjahr == DateTime.Now.Year);
            IQueryable<Fk_sjfs> Fk_sjfs = DBServerProvider.DbContext.Set<Fk_sjfs>().Where(w => w.budat.Value.Year == DateTime.Now.Year);

            decimal ?sum_sj = new decimal();
            decimal ?sum_yuan = new decimal();
            sum_yuan=Fk_yusuan_view_unpivot.Sum(w => w.je);
            sum_sj = Fk_sjfs.Sum(w => w.hsl);
            //sum_sj =
            string q_racct = racct;
            string q_cntr = rcntr;
            IQueryable<Fk_yusuan> fys = null;
            IQueryable<Fk_sjfs> sjfs = null;
            int q_year = DateTime.Now.Year;
            if ((q_racct == "all") && (q_cntr != "all"))
            {
                fys = Fk_yusuanRepository.Instance.FindAsIQueryable(t => t.rcntr == q_cntr && t.gjahr == q_year);
                sjfs = Fk_sjfsRepository.Instance.FindAsIQueryable(t => t.rcntr == q_cntr && t.budat.Value.Year == q_year);
            }
            if ((q_racct != "all") && (q_cntr == "all"))
            {
                fys = Fk_yusuanRepository.Instance.FindAsIQueryable(t => t.racct == q_racct && t.gjahr == q_year);
                sjfs = Fk_sjfsRepository.Instance.FindAsIQueryable(t => t.racct == q_racct && t.budat.Value.Year == q_year);
            }

            if ((q_racct == "all") && (q_cntr == "all"))
            {
                fys = Fk_yusuanRepository.Instance.FindAsIQueryable(t => (1 == 1) && t.gjahr == q_year);
                sjfs = Fk_sjfsRepository.Instance.FindAsIQueryable(t => (1 == 1) && t.budat.Value.Year == q_year);
            }

            if ((q_racct != "all") && (q_cntr != "all"))
            {
                fys = Fk_yusuanRepository.Instance.FindAsIQueryable(t => (t.racct == q_racct && t.rcntr == q_cntr && t.gjahr == q_year));
                sjfs = Fk_sjfsRepository.Instance.FindAsIQueryable(t => (t.racct == q_racct && t.rcntr == q_cntr && t.budat.Value.Year == q_year));
            }

          
            decimal?[] sum_yu_list = new decimal?[12];
            sum_yu_list[0] = fys.Sum(t => (t.tsl01));
            sum_yu_list[1] = fys.Sum(t => (t.tsl02));
            sum_yu_list[2] = fys.Sum(t => (t.tsl03));
            sum_yu_list[3] = fys.Sum(t => (t.tsl04));
            sum_yu_list[4] = fys.Sum(t => (t.tsl05));
            sum_yu_list[5] = fys.Sum(t => (t.tsl06));
            sum_yu_list[6] = fys.Sum(t => (t.tsl07));
            sum_yu_list[7] = fys.Sum(t => (t.tsl08));
            sum_yu_list[8] = fys.Sum(t => (t.tsl09));
            sum_yu_list[9] = fys.Sum(t => (t.tsl10));
            sum_yu_list[10] = fys.Sum(t => (t.tsl11));
            sum_yu_list[11] = fys.Sum(t => (t.tsl12));


         
            decimal?[] sum_sj_list = new decimal?[12];

            //sum_sj_list[0] = sjfs.Sum(t => (t.hsl));
            sum_sj_list[0] = sjfs.Where(w => w.budat.Value.Month == 1 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[1] = sjfs.Where(w => w.budat.Value.Month == 2 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[2] = sjfs.Where(w => w.budat.Value.Month == 3 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[3] = sjfs.Where(w => w.budat.Value.Month == 4 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[4] = sjfs.Where(w => w.budat.Value.Month == 5 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[5] = sjfs.Where(w => w.budat.Value.Month == 6 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[6] = sjfs.Where(w => w.budat.Value.Month == 7 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[7] = sjfs.Where(w => w.budat.Value.Month == 8 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[8] = sjfs.Where(w => w.budat.Value.Month == 9 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[9] = sjfs.Where(w => w.budat.Value.Month == 10 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[10] = sjfs.Where(w => w.budat.Value.Month == 11 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj_list[11] = sjfs.Where(w => w.budat.Value.Month == 12 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));

            rtn_hst.Add("sum_sj", Math.Round(sum_sj / 10000 ?? 0, 2));//实际发生总额（单位万）
            rtn_hst.Add("sum_yuan", Math.Round(sum_yuan / 10000 ?? 0, 2));//预算总额（单位万）

            for(int i=0;i<= sum_yu_list.Length - 1; i++)
            {
                sum_yu_list[i] = Math.Round(sum_yu_list[i] / 10000 ?? 0, 2);
            }
            for (int i = 0; i <= sum_sj_list.Length - 1; i++)
            {
                sum_sj_list[i] = Math.Round(sum_sj_list[i] / 10000 ?? 0, 2);
            }

            string sql = "";
            //9000的生产成本
            sql = "select monat,amout from COST_ACCOUNT_BUSINESS_INCOME where ryear=to_char(sysdate,'yyyy') and rbukrs='9000' order by to_number(monat) asc";
            sql = sql.Replace("'", "''");
            //List<dynamic> dditem=Other_Oracle_Helper.query<dynamic>(sql, "dms");//订单明细,通过第三方连接取
            List<dynamic> yue_cenb = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([117],'" + sql + "')", "").ToList(); ;//订单明细,通过dblink取数，SQL运行的是oracle预警


            //8000的收入
            sql = "select monat,amout*(-1) amout from COST_ACCOUNT_BUSINESS_INCOME where ryear=to_char(sysdate,'yyyy') and rbukrs='8000' order by to_number(monat) asc";
            sql = sql.Replace("'", "''");
            //List<dynamic> dditem=Other_Oracle_Helper.query<dynamic>(sql, "dms");//订单明细,通过第三方连接取
            List<dynamic> yue_shour = DBServerProvider.SqlDapper.QueryList<dynamic>("select * from openquery([117],'" + sql + "')", "").ToList(); ;//订单明细,通过dblink取数，SQL运行的是oracle预警

            rtn_hst.Add("yue_cenb", yue_cenb.Select(s=> Math.Round(Convert.ToDouble(s.AMOUT)/10000,2)));//9000的生产成本
            rtn_hst.Add("yue_shour", yue_shour.Select(s => Math.Round(Convert.ToDouble(s.AMOUT)/10000,2)));//8000的收入

            rtn_hst.Add("sum_yu_list", sum_yu_list);//预算每月汇总（单位万）
            rtn_hst.Add("sum_sj_list", sum_sj_list);//实际每月汇总（单位万）

            //获取成本中心和科目的给下拉表
            sql = "select distinct (select a.rcntr from fk_cntr a where a.rcntr_no=fk_sjfs.rcntr) label,fk_sjfs.rcntr value from fk_sjfs where  year(GETDATE())=year(fk_sjfs.budat)";
            dynamic[] array_rcntrlist = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();
            rtn_hst.Add("array_rcntrlist", array_rcntrlist);


            sql = "select distinct (select a.fei_typename from fei_typelist a where a.racct=fk_sjfs.racct) label,fk_sjfs.racct value from fk_sjfs where year(GETDATE())=year(fk_sjfs.budat)";
            dynamic[] array_racctlist = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();
            rtn_hst.Add("array_racctlist", array_racctlist);

            return rtn_hst;
        }
        [HttpPost, HttpGet, Route("km_f"), AllowAnonymous]
        public object Km_f(int yue,string rcntr)//科目分类
        {
            Hashtable rtn_hst = new Hashtable();
            string sql = "select  (select a.fei_typename from fei_typelist a where a.racct=fk_sjfs.racct) racct,sum(hsl) je,fk_sjfs.rcntr rcntr from fk_sjfs where year(GETDATE())=year(fk_sjfs.budat) and MONTH(fk_sjfs.budat)=" + yue + " and  racct in(select racct from fk_yusuan )  group by racct,rcntr order by sum(hsl)";
            if (yue == 0)//全年
            {
                sql = "select  (select a.fei_typename from fei_typelist a where a.racct=fk_sjfs.racct) racct,sum(hsl) je,fk_sjfs.rcntr rcntr from fk_sjfs where year(GETDATE())=year(fk_sjfs.budat)  and  racct in(select racct from fk_yusuan )  group by racct,rcntr order by sum(hsl)";
            }
          //  string sql = "select (select a.fei_typename from fei_typelist a where a.racct=fk_sjfs.racct) racct,sum(hsl) je from fk_sjfs where year(GETDATE())=year(fk_sjfs.budat) and MONTH(fk_sjfs.budat)=" + yue + " and  racct in(select racct from fk_yusuan )  group by racct order by sum(hsl)";
            dynamic[] racct_list = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();
            if (rcntr == "all")
            {
              var t=  racct_list.GroupBy(x => new{ x.racct }).Select(s =>new {
                    racct = s.Key.racct,
                    je = s.Sum(b=>Convert.ToInt32(b.je??0.00))}).OrderBy(e=>e.je).ToList();
                rtn_hst.Add("racct_list", t);
            }
            else
            {
                var t = racct_list.Where(t => t.rcntr == rcntr).GroupBy(x => new { x.racct,x.rcntr }).Select(s => new {
                    racct = s.Key.racct,
                    rcntr = s.Key.rcntr,
                    je = s.Sum(b => Convert.ToInt32(b.je??0.00))
                }).ToList();
                rtn_hst.Add("racct_list", t);
            }
           
            return rtn_hst;
        }

        [HttpPost, HttpGet, Route("Cntr_f"), AllowAnonymous]
        public object Cntr_f(int yue,string racct)//成本中心分类
        {
            Hashtable rtn_hst = new Hashtable();
            string sql = "select (select a.rcntr from fk_cntr a where a.rcntr_no=fk_sjfs.rcntr) rcntr,sum(hsl) je,fk_sjfs.racct from fk_sjfs where  year(GETDATE())=year(fk_sjfs.budat) and MONTH(fk_sjfs.budat)=" + yue + " and  racct in(select racct from fk_yusuan )  group by rcntr,racct order by sum(hsl)";
            if (yue == 0)//全年
            {
                sql = "select (select a.rcntr from fk_cntr a where a.rcntr_no=fk_sjfs.rcntr) rcntr,sum(hsl) je,fk_sjfs.racct from fk_sjfs where year(GETDATE())=year(fk_sjfs.budat) and  racct in(select racct from fk_yusuan )  group by rcntr,racct order by sum(hsl)";
            }
            //  string sql = "select (select a.fei_typename from fei_typelist a where a.racct=fk_sjfs.racct) racct,sum(hsl) je from fk_sjfs where year(GETDATE())=year(fk_sjfs.budat) and MONTH(fk_sjfs.budat)=" + yue + " and  racct in(select racct from fk_yusuan )  group by racct order by sum(hsl)";
            dynamic[] rcntr_list = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();

            if (racct == "all")
            {
                var t = rcntr_list.GroupBy(x => new { x.rcntr }).Select(s => new {
                    rcntr = s.Key.rcntr,
                    je = s.Sum(b => Convert.ToInt32(b.je ?? 0.00))
                }).OrderBy(e => e.je).ToList();
                rtn_hst.Add("rcntr_list", t);
            }
            else
            {
                var t = rcntr_list.Where(t => t.racct == racct).GroupBy(x => new { x.racct, x.rcntr }).Select(s => new {
                    racct = s.Key.racct,
                    rcntr = s.Key.rcntr,
                    je = s.Sum(b => Convert.ToInt32(b.je ?? 0.00))
                }).ToList();
                rtn_hst.Add("rcntr_list", t);
            }
            return rtn_hst;
        }


    }
}
